ROW 还是 STATEMENT?线上 MySQL Binlog 怎么选?
点击上方“民工哥技术之路”选择“星标”
每天10点为你分享不一样的干货
前段时间,有群友在群里问起此问题,生产环境中对于Mysql Binlog的模式选择,到底是选ROW模式、还是STATEMENT?再或者是MIXED模式?
文章有点长,建议先转发到朋友圈后慢慢阅读、理解,再操作实践,你会发现对于这两种模式的选择,你会有一个很清楚的认识。
mysql-binlog是MySQL数据库的二进制日志,用于记录用户对数据库操作的SQL语句((除了数据查询语句)信息。可以使用mysqlbin命令查看二进制日志的内容。
二进制日记录了数据库执行更改的操作,如Insert,Update,Delete等。不包括Select等不影响数据库记录的操作,因为没有对数据进行修改。二进制主要的功能有:复制(Replication)和恢复(Recovery)。
MySQL Binlog三种格式介绍
binlog的格式也有三种:STATEMENT、ROW、MIXED 。
1、STATMENT模式:基于SQL语句的复制(statement-based replication, SBR),每一条会修改数据的sql语句会记录到binlog中。
优点:不需要记录每一条SQL语句与每行的数据变化,这样子binlog的日志也会比较少,减少了磁盘IO,提高性能。
缺点:在某些情况下会导致master-slave中的数据不一致(如sleep()函数, last_insert_id(),以及user-defined functions(udf)等会出现问题)
2、基于行的复制(row-based replication, RBR):不记录每一条SQL语句的上下文信息,仅需记录哪条数据被修改了,修改成了什么样子了。
优点:不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。
缺点:会产生大量的日志,尤其是alter table的时候会让日志暴涨。
3、混合模式复制(mixed-based replication, MBR):以上两种模式的混合使用,一般的复制使用STATEMENT模式保存binlog,对于STATEMENT模式无法复制的操作使用ROW模式保存binlog,MySQL会根据执行的SQL语句选择日志保存方式。
所以本文,就这三种模式有何区别,在生产如何选择,做一个具体的案例详解。
日志文件大小
三个客户端的数据如下:
Client1
root@localhost : test 11:33:58>show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.00 sec)
root@localhost : test 11:34:01>select count(*) from me_info;
+----------+
| count(*) |
+----------+
| 84183 |
+----------+
1 row in set (0.00 sec)
二进制日志:106个字节。
-rw-rw---- 1 mysql adm 106 2012-12-28 14:44 mysql-bin.000001
Client2
root@127.0.0.1 : test 11:34:23>show variables like 'binlog_format';
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)
root@127.0.0.1 : test 11:34:25>select count(*) from me_info;
+----------+
| count(*) |
+----------+
| 84183 |
+----------+
1 row in set (0.00 sec)
二进制日志:106字节
-rw-rw---- 1 mysql adm 106 2012-12-28 14:44 mysql-bin2.000001
Client3
root@127.0.0.1 : test 04:09:07>show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec)
root@127.0.0.1 : test 04:09:14>select count(*) from me_info;
+----------+
| count(*) |
+----------+
| 84183 |
+----------+
1 row in set (0.00 sec)
-rw-rw---- 1 mysql adm 33 2012-12-31 16:15 mysql-bin3.index
除了binlog_format不一样之外,其他都是一样的。先看下事务操作的日志大小(物理)。删除数据:
delete from me_info where id < 2153269;
查看他们日志的大小:发现ROW 和 其他2个大小不一致,而MIXED和STATEMENT一致。通过mysqlbinlog 发现他们记录的格式ROW不同于STATEMENT和MIXED。
-rw-rw---- 1 mysql adm 3.7M 2012-12-31 16:19 mysql-bin.000001
-rw-rw---- 1 mysql adm 207 2012-12-31 16:19 mysql-bin2.000001
-rw-rw---- 1 mysql adm 207 2012-12-31 16:19 mysql-bin3.000001
小结1:
通过上面的说明得出一点是ROW格式比MIX和STATEMENT要大,原因是ROW记录的是记录更新后的值(不需要记录上下文信息),而其他2个模式记录的只是一个逻辑的SQL语句(需要记录上下文信息),具体格式可以看这里的ROW日志信息。因为上面的表删除了3W的记录,ROW模式会记录每一条删除语句,所以日志会很大。这也说明将格式设置成ROW,对于磁盘空间的要求增加了,而复制采用传输二进制日志方式实现的,所以复制的网络开销也有增加。所以最后的结果是:ROW>STATEMENT=MIXED
对数据复制产生的影响
测试表:
root@127.0.0.1 : rep_test 05:38:06>desc user;
+---------------+--------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | | |
| status | int(4) | YES | MUL | NULL | |
…………………………………………
…………………………………………
…………………………………………
+---------------+--------------+------+-----+---------------------+----------------+
42 rows in set (0.03 sec)
1、测试对磁盘IOPS,网卡流量,cpu的影响
使用一个更新脚本,更新一个字符串。
import MySQLdb
from random import choice
from random import randint
def get_str(n):
A=''
for i in range(n):
A=A+chr(97+randint(0,25))
return A
if __name__ =='__main__':
pwd = get_str(16)
conn = MySQLdb.connect(host='localhost',user='root',passwd='123456',charset='utf8',db='rep_test')
for i in xrange(1000000):
query ="update user set password = '%s' where id =%d" %(pwd,i)
cursor = conn.cursor()
cursor.execute(query)
print query
print 'OK'
执行脚本,查看网卡流量:{iftop、ifstat、dstat -N eth0 }
STATEMENT下主从的情况:
STATEMENT
-rw-rw---- 1 mysql adm 128M 2013-01-15 09:29 mysql-bin.000001
主:
从:
从上面信息可以看出:产生了128M的二进制日志,在复制期间,Master网卡出去(send)流量平均1M左右,Slave网卡接收(recv)流量平均1M左右,Master的CPU空闲30左右,Slave的CPU空闲30~40,磁盘读写都比较小。
ROW下主从的情况:
ROW:
-rw-rw---- 1 mysql adm 706M 2013-01-15 09:37 mysql-bin.000002
主:
从:
从上面信息可以看出:产生了706M的二进制日志,在复制期间,Master网卡出去(send)流量4M~5M,Slave网卡接收(recv)流量4M~5M,Master的CPU空闲20~30,Slave的CPU空闲20左右,磁盘读写也不算大。
对比Row和Statement:R比S产生的日志量大5.5倍,网卡流量高4~5倍,cpu稍微忙了10个百分点。在复制过程中,从均没有延迟。因为SQL过滤条件WHERE 后面的字段利用好索引,ROW和STATEMENT模式下效果一样。要是没有利用好索引,则:
STATEMENT下:在主上执行(3~5s)一条,从上也是需要这个时间,并且出现延迟。(Seconds_Behind_Master)。本来就单线程的,导致从的可用性更差。
ROW下:在主上执行(3~5s)一条,正常情况下每张表都有主键,所以按照ROW的记录的SQL格式,不会出现对这类sql的延迟。除非极端情况下更新一张没有主键甚至没有任何索引的表。
范围内的批量更新结果怎么样?
update user set password = 'serqrnncavfyozeu' where id > 0 and id < 1000000
STATEMENT下主从的情况:
MIXED:
-rw-rw---- 1 mysql adm 253 2013-01-15 10:32 mysql-bin.000005
主:
----total-cpu-usage---- -dsk/total- --net/eth0- ---paging-- ---system--
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
1 93 0 0 0| 27k 119k| 0 0 | 0 0 | 630 1346
16 40 14 0 0| 0 43M| 20k 594B| 0 0 |1175 2851
25 32 2 0 0| 0 44k|2892B 126B| 0 0 | 985 2651
25 43 0 0 1| 0 25M|2623B 192B| 0 0 |1035 2008
25 43 0 0 0| 0 12M| 18k 1116B| 0 0 | 981 2184
24 45 0 0 0| 0 0 |1486B 66B| 0 0 | 804 1777
27 43 0 0 0| 0 0 |2285B 66B| 0 0 | 836 1841
28 32 1 0 0| 0 12k| 12k 1056B| 0 0 | 923 2629
10 72 2 0 0| 0 32k|2017B 464B| 0 0 |1208 2311
从:
----total-cpu-usage---- -dsk/total- --net/eth0- ---paging-- ---system--
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
2 87 0 0 0| 56k 47k| 0 0 | 0 0 |1499 1703
1 97 1 0 0| 0 62k|1208B 932B| 0 0 | 476 835
1 56 0 0 0| 0 512B|3402B 354B| 0 0 |1260 748
1 52 0 0 0| 0 0 |2544B 17k| 0 0 |1417 1091
2 97 0 0 0| 0 0 |1893B 354B| 0 0 | 586 847
1 95 0 0 0| 0 0 |1922B 468B| 0 0 | 488 680
1 49 0 0 1| 0 0 |2253B 17k| 0 0 |1729 1349
1 66 1 0 0| 0 13k|1733B 354B| 0 0 | 834 565
1 97 0 0 0| 0 0 |2571B 354B| 0 0 | 485 679
1 80 0 0 0| 0 0 |2438B 10k| 0 0 |1110 1232
ROW下主从的情况:
-rw-rw---- 1 mysql adm 430M 2013-01-15 10:28 mysql-bin.000004
主:
----total-cpu-usage---- -dsk/total- --net/eth0- ---paging-- ---system--
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
1 93 0 0 0| 26k 116k| 0 0 | 0 0 | 629 1346
15 36 11 0 0| 0 45M|5389B 576B| 0 0 |1226 2611
15 44 0 0 1| 0 50M|2406B 192B| 0 0 | 945 1807
18 43 0 0 0| 0 49M|3046B 132B| 0 0 | 903 1945
20 29 0 0 0| 0 49M| 13k 1056B| 0 0 |1137 2528
18 45 0 0 0| 0 51M|2137B 66B| 0 0 |1014 1799
12 42 16 0 0| 0 44M|3315B 192B| 0 0 |1120 2265
18 40 4 0 1| 0 47M| 11k 1056B| 0 0 |1030 1875
17 43 0 0 1| 0 52M|2783B 66B| 0 0 |1000 1833
从:
----total-cpu-usage---- -dsk/total- --net/eth0- ---paging-- ---system--
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
2 87 0 0 0| 56k 47k| 0 0 | 0 0 |1499 1703
1 49 0 0 0| 0 0 |1446B 834B| 0 0 | 952 348
0 66 0 0 0| 0 0 |2706B 468B| 0 0 |1180 895
1 98 0 0 1| 0 0 |4082B 10k| 0 0 | 577 997
1 80 0 0 0| 0 0 |2083B 354B| 0 0 | 683 583
1 49 0 0 0| 0 0 |2413B 354B| 0 0 |1323 719
2 77 1 0 1| 0 13k|2557B 10k| 0 0 | 714 902
1 96 0 0 0| 0 0 |2565B 354B| 0 0 | 602 867
1 68 0 0 0| 0 0 |2282B 354B| 0 0 |1273 971
对比发现:在执行此类sql的时候,在STATEMENT下面,(利用好索引)主和从的各个开销都很小,网络流量都不大。而在ROW下面:因为日志产生量就很大,导致在复制期间网卡流量就很大:12M。网卡流量:【1:10000】,日志大小:【1:2000000】,CPU空闲:【80:20】。这个只限于这个例子,看范围大小和表字段的大小。总之在网络和磁盘开销上面比较,他们差距了好几个数量级。
小结2:
对于更新单条的sql语句,在STATEMENT和ROW下
1,CPU消耗差距不大,都需要执行这么sql。消耗 R=S
2,磁盘写和网络传输上,因为ROW记录的格式的原因。消耗 R>S
3,SQL效率来看,合理利用索引的更新,效率差距不大,不合理利用索引的更新,效率 R>S
4,日志文件大小上,因为都需要记录这么多SQL,但是由于R和S的记录格式不一样,大小 R>S
对于执行一个大范围的sql语句,在STATEMENT和ROW下
1,CPU上,主上只要执行一条SQL,而从上需要执行N条,消耗 R>S
2,磁盘写和网络传输上,因为ROW记录的格式的原因。消耗R>S,看范围条件,大的话,差距巨大。
3,日志文件大小上,主记录一条,从记录N条,并且还由于R和S的记录格式不一样,R>S,差距巨大。
从上面的分析得出,STATEMENT要比ROW划算。要是使用STATEMENT没有任何问题的话,就推荐使用STATEMENT/MIXED格式记录二进制日志。
2,数据的一致性:
其实ROW有很多一些好处。特别对数据的一致性有了很严的要求。
情况1:
STATEMENT/MIXED
主:
从:
主库上执行下列操作:
root@localhost : rep_test 11:25:11>update tt set name =upper(name) where id =7;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
从库查看:
root@127.0.0.1 : rep_test 11:25:01>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 450
Relay_Log_File: zhoujy-relay-bin.000002
Relay_Log_Pos: 595
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: test
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 450
Relay_Log_Space: 751
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
ROW模式,主上执行下面的操作:
root@localhost : rep_test 11:25:11>update tt set name =upper(name) where id =7;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
从库是查看:
root@127.0.0.1 : rep_test 11:25:47>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 687
Relay_Log_File: zhoujy-relay-bin.000002
Relay_Log_Pos: 595
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: test
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Could not execute Update_rows event on table rep_test.tt; Can't find record in 'tt', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000001, end_log_pos 614
Skip_Counter: 0
Exec_Master_Log_Pos: 450
Relay_Log_Space: 988
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Update_rows event on table rep_test.tt; Can't find record in 'tt', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000001, end_log_pos 614
更新主上有的数据,但从上没有:在STATEMENT/MIXED下,复制正常,没有报错。而在ROW下,复制终止。
情况2:和ROW记录的格式有关
STATEMENT/MIXED
把从的name字段从varchar 改成 char
主:
insert into tt values(8,'H'),(9,'I');
从:复制成功
ROW 主上执行:
root@localhost : rep_test 12:41:55>insert into tt values(10,'J');
Query OK, 1 row affected (0.00 sec)
从上查看复制同步:
root@127.0.0.1 : rep_test 12:41:59>show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 658
Relay_Log_File: zhoujy-relay-bin.000011
Relay_Log_Pos: 574
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: test
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1535
Last_Error: Table definition on master and slave does not match: Column 1 type mismatch - received type 15, rep_test.tt has type 254
Skip_Counter: 0
Exec_Master_Log_Pos: 429
Relay_Log_Space: 1002
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1535
Last_SQL_Error: Table definition on master and slave does not match: Column 1 type mismatch - received type 15, rep_test.tt has type 25
主从上的字段属性不一样,在STATEMENT/MIXED下,不受影响,复制正常,而在ROW下,复制报错。varcar <=> char
主从上的字段长度不一样,在STATEMENT/MIXED下,不受影响,复制正常,而在ROW下,复制报错。varchar(10) <=> varchar(20)
对于情况2,在5.1里面没有办法自动处理复制的错误,但是在5.5版本中增加了一个参数控制:slave_type_conversions
ALL_LOSSY:仅支持有损转换,比如一个值本来是bigint存储为9999999999999,现在转换为int类型势必会要截断从而导致数据不一致。
ALL_NON_LOSSY:仅支持无损转换,只能在无损的情况下才能进行转换
ALL_LOSSY,ALL_NON_LOSSY:有损/无算转换都支持
空,即不设置这个参数:必须主从的字段类型一模一样。
表示允许相同类型字段、长度不同,否则默认为空,会导致主从停止
zjy@localhost : test 01:52:45>show variables like 'slave_type%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| slave_type_conversions | |
+------------------------+-------+
zjy@localhost : test 01:53:00>set global slave_type_conversions ='ALL_LOSSY,ALL_NON_LOSSY';
Query OK, 0 rows affected (0.00 sec)
zjy@localhost : test 01:53:30>show variables like 'slave_type%';
+------------------------+-------------------------+
| Variable_name | Value |
+------------------------+-------------------------+
| slave_type_conversions | ALL_LOSSY,ALL_NON_LOSSY |
+------------------------+-------------------------+
在从上修改了之后,情况2的复制报错不会再出现。
目前只发现这2个,后期发现再补充进来。
小结3:
对于ROW和STATEMENT的复制,ROW在数据的一致性上面要求更好,从库要是提供服务,最好把复制模式改成ROW。
对于ROW和STATEMENT下的存储过程,函数,触发器,事件的记录方式有什么区别呢?结果:
触发器(TRIGGER):
ROW
主上有,从上没有,复制正常,数据正常。
主上有,从上也有,复制正常,数据正常。
STATEMENT/MIXED
主上有,从上没有,复制正常,数据不正常,触发器里面的sql语句没有执行。
主上有,从上也有,复制正常,数据正常。
函数(FUNCTION):
ROW
主上有,从上没有,复制正常,数据正常。日志里记录的是UDF转换过的结果。
主上有,从上也有,复制正常,数据正常。
STATEMENT/MIXED
主上有,从上没有,复制报错。从不识别UDF函数。
主上有,从上也有,复制正常,数据正常。
存储过程(STORED PROCEDURES)
ROW
主上有,从上没有,复制正常,数据正常。记录的不是call sp,而是SP里面的sql。
主上有,从上也有,复制正常,数据正常。
STATEMENT/MIXED
主上有,从上没有,复制正常,数据正常。记录的不是call sp,而是SP里面的sql。
主上有,从上也有,复制正常,数据正常。
事件(event):
ROW
主上有,从上没有,复制正常,数据正常。记录的不是计划,而是EVENT里面的sql。
主上有,从上也有,复制正常,数据正常。(默认,DISABLE ON SLAVE),ALTER EVENT event_name ENABLE/DISABLE
STATEMENT/MIXED
主上有,从上没有,复制正常,数据正常。记录的不是计划,而是EVENT里面的sql。
主上有,从上也有,复制正常,数据正常。(默认,DISABLE ON SLAVE), ALTER EVENT event_name ENABLE/DISABLE
小结4:
Event最好在主上,其他的都可以在主从上同时存在,要是人为的操作数据库而修改模式(R-S)也不会出现问题,更能确保数据的一致性。
更新一个大范围的SQL,针对STATEMENT没有什么疑问。对比ROW,其磁盘写和网卡流量以及CPU消耗都比较大,特别是一大个范围的sql语句,差距很大,这时候用STATMENT相对来说更好,因为在利用好索引的前提下,STATEMENT更划算。如上面的例子。
更新小数据,比如每次sql更新一条或则几条,ROW和STATMENT差距不是很大。虽然有几倍的差距,但是这些影响对目前的设备来讲也没任何压力。而且利用ROW之后,可以使没有利用好索引的sql,在从上能更好的执行,并且更能保证主从数据的一致性,更诱人的是ROW下可以实现误操作回退的功能。
所以权衡下,有大范围的更新(一般线上很少),人为的去执行,在执行前,把当前session设置成STATEMENT,其余的都用ROW。这样就避免了上面所说的情况。要是线上有这类操作的话,可以让程序先执行 :
set session binlog_format=mixed;
作者:jyzhou
原文:https://www.cnblogs.com/zhoujinyi/archive/2013/01/15/2836131.html
部分内容民工哥做了相关的编辑与修改、补充。
关注 民工哥技术之路 微信公众号对话框回复关键字:1024 可以获取一份最新整理的技术干货:包括系统运维、数据库、redis、MogoDB、电子书、Java基础课程、Java实战项目、架构师综合教程、架构师实战项目、大数据、Docker容器、ELK Stack、机器学习、BAT面试精讲视频等。
一文速查 Git 常用命令,搞定版本控制照做就ok|新手福利
点击【阅读原文】发现更多精彩内容~~
在看的你,请点这里↓↓↓